/*
	EXEC SP_LOAD_LOTS_FOR_PO_RECEIPT '2', 12, 16, 8, 8
	EXEC SP_LOAD_LOTS_FOR_PO_RECEIPT '2', 0, 16, 0, 8
	EXEC SP_LOAD_LOTS_FOR_PO_RECEIPT '2', 1, 16, -1, 8
	EXEC SP_LOAD_LOTS_FOR_PO_RECEIPT '2', 12, 16, -1, -1
*/
ALTER PROC SP_LOAD_LOTS_FOR_PO_RECEIPT @PO_NUMBER VARCHAR(50),
					@PROCESSSTEP_ID INT,
					@VENDOR_ID INT,
					@RECEIPT_LINEITEM_ID INT,
					@RECEIPT_ID INT
AS
BEGIN
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @LOCATION_ID INT
	DECLARE @PRM_RECEIPT_LINEITEM_ID VARCHAR(50)

	IF ( @RECEIPT_LINEITEM_ID > 0 )
	 BEGIN
		SET @PRM_RECEIPT_LINEITEM_ID = @RECEIPT_LINEITEM_ID
	 END
	ELSE
	 BEGIN
		SET @PRM_RECEIPT_LINEITEM_ID = '%'
	 END
	--PRINT @PRM_RECEIPT_LINEITEM_ID
	
	DECLARE @RESULTS TABLE( LOT_ID INT, LOT_NUMBER VARCHAR(50), QTY_IN INT, Qty_Pulled INT,  DATE_IN DATETIME, LOT_CURRENT_STATUS VARCHAR(50), PO_NUMBER VARCHAR(50), IS_SELECTED INT, PROCESSSTEP_ID INT, PS_TYPE_NAME VARCHAR(50), PART_NUMBER VARCHAR(50), RECEIPT_LINEITEM_ID INT )
	SELECT @PS_TYPE_NAME=PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @VENDOR_ID AND L_TYPE = @PS_TYPE_NAME

	IF ( @PROCESSSTEP_ID > 0 )
	BEGIN
		--LOAD LOTS WHICH MATCHES PO_NUMBER AND WHICH DOES NOT HAVE PO_NUMBER FOR THE VENDOR
		--SET SELECTED TRUE FOR THE LOTS WHICH HAS PO NUMBER
		INSERT INTO @RESULTS 
		SELECT LT.LOT_ID, LT.LOT_NUMBER, ( ISNULL((SELECT MAX(ISNULL( Qty_IN, Wafer_Qty_IN )) FROM Lot_Table_History WHERE Lot_Id = Lt.lot_ID ), ISNULL( QTY_In, Wafer_Qty_In ) )- ( SELECT ISNULL( SUM( QTY_IN ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID = LT.Lot_Id AND RECEIPT_LINEITEM_ID != @RECEIPT_LINEITEM_ID ) ) AS QTY_IN, 
		( CASE WHEN PO_NUMBER IS NOT NULL OR PO_NUMBER = @PO_NUMBER THEN ( ISNULL((SELECT MAX(ISNULL( Qty_IN, Wafer_Qty_IN )) FROM Lot_Table_History WHERE Lot_Id = Lt.lot_ID ), ISNULL( QTY_In, Wafer_Qty_In ) )- ( SELECT ISNULL( SUM( QTY_IN ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID = LT.Lot_Id AND RECEIPT_LINEITEM_ID != @RECEIPT_LINEITEM_ID ) ) ELSE 0 END ), LT.DATE_IN, Lt.LOT_CURRENT_STATUS, LT.PO_NUMBER,
			( CASE WHEN @RECEIPT_LINEITEM_ID > 0 OR (PO_NUMBER IS NULL OR PO_NUMBER = '') THEN 1 ELSE 0 END ) AS IS_SELECTED, LT.PROCESSSTEP_ID, (SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID) AS PS_TYPE_NAME, (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID) AS PART_NUMBER, -1 
				FROM LOT_TABLE LT WHERE LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND LT.LOCATION_ID = @LOCATION_ID 
					--AND LOT_ID NOT IN ( SELECT LOT_ID FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID IS NOT NULL )
					AND LT.PO_RECEIPT_ID <= 0 
					AND ( LT.PO_NUMBER = @PO_NUMBER OR LT.PO_NUMBER IS NULL OR LT.PO_NUMBER = '' ) AND LT.Lot_ID NOT IN ( SELECT LOT_ID FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE RECEIPT_LINEITEM_ID = @RECEIPT_LINEITEM_ID  )
		UNION 
		SELECT PORLT.LOT_ID, PORLT.LOT_NUMBER, ( ISNULL((SELECT MAX(ISNULL( Qty_IN, Wafer_Qty_IN )) FROM Lot_Table_History WHERE Lot_Id = Lt.lot_ID ), ISNULL( Lt.QTY_In, Lt.Wafer_Qty_In ) )- ( SELECT ISNULL( SUM( QTY_IN ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID = LT.Lot_Id AND RECEIPT_LINEITEM_ID != @RECEIPT_LINEITEM_ID ) ) AS QTY_IN, PORLT.QTY_IN, LT.DATE_IN, LT.LOT_CURRENT_STATUS, LT.PO_NUMBER, 0 AS IS_SELECTED, LT.PROCESSSTEP_ID, (SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PS_TYPE_NAME, (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PART_NUMBER, PORLIT.RECEIPT_LINEITEM_ID
				 FROM PURCHASEORDER_RECEIPT_LOT_TABLE PORLT
				LEFT OUTER JOIN LOT_TABLE LT ON LT.LOT_ID= PORLT.LOT_ID				
				JOIN PURCHASEORDER_RECEIPT_LINEITEM_TABLE PORLIT ON PORLT.RECEIPT_LINEITEM_ID = PORLIT.RECEIPT_LINEITEM_ID
			WHERE PORLT.RECEIPT_LINEITEM_ID LIKE @PRM_RECEIPT_LINEITEM_ID
				AND PORLIT.RECEIPT_ID = @RECEIPT_ID AND PORLIT.ProcessStep_ID = @PROCESSSTEP_ID 
	 END
	ELSE --FOR OPEN PO LOT RECEIPTS
	 BEGIN
		INSERT INTO @RESULTS 
		SELECT LT.LOT_ID, LT.LOT_NUMBER, ( ISNULL( ( SELECT MAX(ISNULL( Qty_IN, Wafer_Qty_IN )) FROM Lot_Table_History WHERE Lot_Id = Lt.lot_ID ), ISNULL( QTY_In, Wafer_Qty_In ) )- ( SELECT ISNULL( SUM( QTY_IN ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID = LT.Lot_Id AND RECEIPT_LINEITEM_ID != @RECEIPT_LINEITEM_ID ) ) AS QTY_IN, 
		( CASE WHEN PO_NUMBER IS NOT NULL OR PO_NUMBER = @PO_NUMBER THEN ( ISNULL( ( SELECT MAX(ISNULL( Qty_IN, Wafer_Qty_IN ) ) FROM Lot_Table_History WHERE Lot_Id = Lt.lot_ID ), ISNULL( QTY_In, Wafer_Qty_In )  )- ( SELECT ISNULL( SUM( QTY_IN ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID = LT.Lot_Id AND RECEIPT_LINEITEM_ID != @RECEIPT_LINEITEM_ID ) ) ELSE 0 END ), LT.DATE_IN, LT.LOT_CURRENT_STATUS, LT.PO_NUMBER,
			( CASE WHEN  @RECEIPT_LINEITEM_ID > 0 OR (LT.PO_NUMBER IS NULL OR LT.PO_NUMBER = '') THEN 1 ELSE 0 END) AS IS_SELECTED, LT.PROCESSSTEP_ID, (SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PS_TYPE_NAME, (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PART_NUMBER, -1 	 
				FROM LOT_TABLE LT, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @VENDOR_ID) 
					--AND LT.LOT_ID NOT IN ( SELECT LOT_ID FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID IS NOT NULL )
					AND  LT.PO_RECEIPT_ID <= 0
					AND ( LT.PO_NUMBER = @PO_NUMBER OR LT.PO_NUMBER IS NULL OR LT.PO_NUMBER = '' )
					AND ( PS.PS_TYPE_NAME = 'FAB' OR PS.PS_TYPE_NAME = 'SORT' OR PS.PS_TYPE_NAME = 'ASSEMBLY' OR PS.PS_TYPE_NAME = 'TEST' ) AND LT.Lot_ID NOT IN ( SELECT LOT_ID FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE RECEIPT_LINEITEM_ID = @RECEIPT_LINEITEM_ID  )
		UNION 
		SELECT PORLT.LOT_ID, PORLT.LOT_NUMBER, ( ISNULL((SELECT MAX(ISNULL( Qty_IN, Wafer_Qty_IN )) FROM Lot_Table_History WHERE Lot_Id = Lt.lot_ID ), ISNULL( Lt.QTY_In, Lt.Wafer_Qty_In )  )- ( SELECT ISNULL( SUM( QTY_IN ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE LOT_ID = LT.Lot_Id AND RECEIPT_LINEITEM_ID != @RECEIPT_LINEITEM_ID ) ) AS QTY_IN,PORLT.QTY_IN, LT.DATE_IN, LT.LOT_CURRENT_STATUS, LT.PO_NUMBER, 0 AS IS_SELECTED, LT.PROCESSSTEP_ID, (SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PS_TYPE_NAME, (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PART_NUMBER, PORLIT.RECEIPT_LINEITEM_ID 
				 FROM PURCHASEORDER_RECEIPT_LOT_TABLE PORLT
				LEFT OUTER JOIN LOT_TABLE LT ON LT.LOT_ID= PORLT.LOT_ID
				JOIN PURCHASEORDER_RECEIPT_LINEITEM_TABLE PORLIT ON PORLT.RECEIPT_LINEITEM_ID = PORLIT.RECEIPT_LINEITEM_ID
			WHERE PORLT.RECEIPT_LINEITEM_ID LIKE @PRM_RECEIPT_LINEITEM_ID
				AND PORLIT.RECEIPT_ID = @RECEIPT_ID			
	 END
	
	SELECT * FROM @RESULTS WHERE QTY_IN > 0 ORDER BY PO_NUMBER, DATE_IN

END
